package com.student.dao;

import com.student.model.Grade;
import com.student.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class GradeDAO {
    
    public List<Grade> findAll() {
        List<Grade> grades = new ArrayList<>();
        String sql = "SELECT g.*, s.name as student_name FROM grade g " +
                    "LEFT JOIN student s ON g.student_number = s.student_number " +
                    "ORDER BY g.student_number, g.course_name";
        
        try (Connection conn = DBUtil.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            
            while (rs.next()) {
                Grade grade = new Grade();
                grade.setId(rs.getInt("id"));
                grade.setStudentNumber(rs.getString("student_number"));
                grade.setStudentName(rs.getString("student_name"));
                grade.setCourseName(rs.getString("course_name"));
                grade.setScore(rs.getDouble("score"));
                grade.setSemester(rs.getString("semester"));
                grade.setExamDate(rs.getDate("exam_date"));
                grade.setCreatedAt(rs.getTimestamp("created_at"));
                grades.add(grade);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return grades;
    }

    public Grade findById(int id) {
        String sql = "SELECT g.*, s.name as student_name FROM grade g " +
                    "LEFT JOIN student s ON g.student_number = s.student_number " +
                    "WHERE g.id = ?";
        
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, id);
            ResultSet rs = pstmt.executeQuery();
            
            if (rs.next()) {
                Grade grade = new Grade();
                grade.setId(rs.getInt("id"));
                grade.setStudentNumber(rs.getString("student_number"));
                grade.setStudentName(rs.getString("student_name"));
                grade.setCourseName(rs.getString("course_name"));
                grade.setScore(rs.getDouble("score"));
                grade.setSemester(rs.getString("semester"));
                grade.setExamDate(rs.getDate("exam_date"));
                grade.setCreatedAt(rs.getTimestamp("created_at"));
                return grade;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public boolean add(Grade grade) {
        String sql = "INSERT INTO grade (student_number, course_name, score, semester, exam_date) " +
                    "VALUES (?, ?, ?, ?, ?)";
        
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, grade.getStudentNumber());
            pstmt.setString(2, grade.getCourseName());
            pstmt.setDouble(3, grade.getScore());
            pstmt.setString(4, grade.getSemester());
            pstmt.setDate(5, new java.sql.Date(grade.getExamDate().getTime()));
            
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    public boolean update(Grade grade) {
        String sql = "UPDATE grade SET student_number=?, course_name=?, score=?, " +
                    "semester=?, exam_date=? WHERE id=?";
        
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, grade.getStudentNumber());
            pstmt.setString(2, grade.getCourseName());
            pstmt.setDouble(3, grade.getScore());
            pstmt.setString(4, grade.getSemester());
            pstmt.setDate(5, new java.sql.Date(grade.getExamDate().getTime()));
            pstmt.setInt(6, grade.getId());
            
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    public boolean delete(int id) {
        String sql = "DELETE FROM grade WHERE id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, id);
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    public List<Grade> search(String studentNumber, String courseName, String semester) {
        List<Grade> grades = new ArrayList<>();
        StringBuilder sql = new StringBuilder(
            "SELECT g.*, s.name as student_name FROM grade g " +
            "LEFT JOIN student s ON g.student_number = s.student_number WHERE 1=1");
        List<Object> params = new ArrayList<>();

        if (studentNumber != null && !studentNumber.trim().isEmpty()) {
            sql.append(" AND g.student_number LIKE ?");
            params.add("%" + studentNumber + "%");
        }
        if (courseName != null && !courseName.trim().isEmpty()) {
            sql.append(" AND g.course_name LIKE ?");
            params.add("%" + courseName + "%");
        }
        if (semester != null && !semester.trim().isEmpty()) {
            sql.append(" AND g.semester = ?");
            params.add(semester);
        }

        sql.append(" ORDER BY g.student_number, g.course_name");

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql.toString())) {
            
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(i + 1, params.get(i));
            }

            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Grade grade = new Grade();
                grade.setId(rs.getInt("id"));
                grade.setStudentNumber(rs.getString("student_number"));
                grade.setStudentName(rs.getString("student_name"));
                grade.setCourseName(rs.getString("course_name"));
                grade.setScore(rs.getDouble("score"));
                grade.setSemester(rs.getString("semester"));
                grade.setExamDate(rs.getDate("exam_date"));
                grade.setCreatedAt(rs.getTimestamp("created_at"));
                grades.add(grade);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return grades;
    }
} 